package com.ryx.server.old;

import com.alibaba.fastjson.JSON;
import com.mysql.jdbc.StringUtils;
import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.UnderlineStyle;
import jxl.write.*;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by lenovo on 2016/10/25.
 */
public class MySqlPlugin implements Plugin {
    private List<DbConfig> configs;

    public MySqlPlugin(List<DbConfig> configs) {
        this.configs = configs;
    }

    public List<DbConfig> getConfigs() {
        return configs;
    }

    public void setConfigs(List<DbConfig> configs) {
        this.configs = configs;
    }

    public MySqlPlugin() {
    }

    public void process() {
        for (int i = 0; i < configs.size(); i++) {
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            List<String> tableName = new ArrayList<>();   //表名
            try {
                DbConfig temp = configs.get(i);
                Class.forName(temp.getDrive());
                conn = DriverManager.getConnection(temp.getUrl(),
                        temp.getUser(), temp.getPwd());
                DatabaseMetaData metaData = conn.getMetaData();
                rs = metaData.getTables(null, null, null, new String[]{"table"});
                while (rs.next()) {
                    tableName.add(rs.getString("TABLE_NAME")); //检索到表名
                    System.out.println(rs.getString("TABLE_NAME"));
                }
                readDbs(tableName, conn, metaData,temp.getDbName());
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }

    private void readDbs(List<String> tableName, Connection conn, DatabaseMetaData data,String dbName) {
        System.out.println("createExcel");
        WritableWorkbook workbook =null;
        //创建工作薄
        try {
            workbook = Workbook.createWorkbook(new File("D:\\log\\"+dbName+".xlsx"));
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("workbook 创建失败！");
            System.exit(-1);
        }
        for (String table : tableName) {
            Statement s1 = null;
            ResultSet r1 = null;
            ResultSetMetaData rsmd = null;
            List<String> tableFieldName = new ArrayList<>();//表的字段
//            List<String> originTableFieldName = new ArrayList<>();//原始表字段
            List<String> dbFieldType = new ArrayList<>();     //表字段的类型
            List<String> columnNote = new ArrayList<>();    //字段备注

            try {
                ResultSet rs = data.getColumns(null, null, table, null);
                while (rs.next()) {
                    columnNote.add(rs.getString("REMARKS"));  //获取字段备注信息
                }
//                if (rs!=null){
//                    rs.close();
//                }
                s1 = conn.createStatement();
                String sql1 = "SELECT * from `" + table + "`";
                r1 = s1.executeQuery(sql1);
                rsmd = r1.getMetaData();
                for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                    tableFieldName.add(rsmd.getColumnName(i)); //获取表字段
                    dbFieldType.add(rsmd.getColumnTypeName(i).toLowerCase());  //获取表字段类型
                }
                System.out.println(table+"--columnNote 注释---  "+JSON.toJSONString(columnNote));
                System.out.println(table+"--tableFieldName 表字段---  "+JSON.toJSONString(tableFieldName));
                System.out.println(table+"--dbFieldType 类型---  "+JSON.toJSONString(dbFieldType));
                System.out.println("---");
                createExcel(columnNote,table,dbFieldType,tableFieldName,workbook);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (r1!=null){
                        r1.close();
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void createExcel(List<String> columnNote,String table,List<String> dbFieldType ,
                             List<String> tableFieldName,WritableWorkbook book) throws WriteException {
        WritableSheet sheet1 = book.createSheet(table, 0);
        WritableCellFormat cellFormat = new WritableCellFormat();
        cellFormat.setFont(new WritableFont(WritableFont.createFont("宋体"),11));
        int col =0;
        int row =0;
        sheet1.addCell(new Label(0,row,"备注",cellFormat));
        sheet1.addCell(new Label(1,row,"表名",cellFormat));
        sheet1.addCell(new Label(2,row,"格式",cellFormat));
        sheet1.addCell(new Label(3,row,"字段",cellFormat));
        for (row++;row<columnNote.size();row++){
            sheet1.addCell(new Label(0,row,columnNote.get(row-1),cellFormat));
            if(row==1){
                sheet1.addCell(new Label(1,row,table,cellFormat));
            }
            sheet1.addCell(new Label(2,row,dbFieldType.get(row-1),cellFormat));
            sheet1.addCell(new Label(3,row,tableFieldName.get(row-1),cellFormat));
        }

    }
}
